﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RealtorsPortal.Data_Access_Layer;
using System.Data.SqlClient;
using System.Data;
using RealtorsPortal.Entities;

namespace RealtorsPortal.Business_Logic_Layer
{
    public class DistrictsDAL
    {
        DataAccessHelper daAccHelp = new DataAccessHelper();

        public DataTable getDistrictsData()
        {
            DataTable dt = new DataTable();
            dt = daAccHelp.GetDataTable("sp_Get_Districts");
            return dt;
        }

        public DataTable getDistrictsDataWithCities()
        {
            DataTable dt = new DataTable();
            dt = daAccHelp.GetDataTable("sp_Get_Districts_With_City_Name");
            return dt;
        }

        public int InsertDistricts(District districtObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryDistrictsToInsert(districtObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Insert_Districts", pList);
        }

        public int UpdateDistricts(District districtObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryDistrictsToUpdate(districtObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Update_Districts", pList);
        }

        public int DeleteDistricts(District districtObj)
        {
            List<SqlParameter> pList = ExecuteNonQueryDistrictsToDelete(districtObj);
            return daAccHelp.ExcuteStoredNonQuery("sp_Delete_Districts", pList);
        }

        public DataTable GetDistrictsByDistrictName(District districtObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@DistrictName", SqlDbType.NVarChar);
            para.Value = districtObj.DistrictName;
            paraList.Add(para);
            dt = daAccHelp.GetData("sp_Search_By_District", paraList);
            return dt;
        }

        public DataTable GetAllByDistrictName(District districtObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@DistrictName", SqlDbType.NVarChar);
            para.Value = districtObj.DistrictName;
            paraList.Add(para);
            dt = daAccHelp.GetData("sp_Get_All_By_Districts", paraList);
            return dt;
        }

        public DataTable GetDistrictById(District districtObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@DistrictId", SqlDbType.Int);
            para.Value = districtObj.DistrictId;
            paraList.Add(para);
            dt = daAccHelp.GetData("sp_Get_District_By_Id", paraList);
            return dt;
        }

        public DataTable GetDistrictByCityId(District districtObj)
        {
            DataTable dt = new DataTable();
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@CityId", SqlDbType.Int);
            para.Value = districtObj.CityId;
            paraList.Add(para);
            dt = daAccHelp.GetData("sp_Get_District_By_CityId", paraList);
            return dt;
        }

        private List<SqlParameter> ExecuteNonQueryDistrictsToInsert(District districtObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@DistrictName", SqlDbType.NVarChar);
            para.Value = districtObj.DistrictName;
            paraList.Add(para);
            para = new SqlParameter("@CityId", SqlDbType.Int);
            para.Value = districtObj.CityId;
            paraList.Add(para);

            return paraList;
        }

        private List<SqlParameter> ExecuteNonQueryDistrictsToDelete(District districtObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;

            para = new SqlParameter("@DistrictId", SqlDbType.Int);
            para.Value = districtObj.DistrictId;
            paraList.Add(para);

            return paraList;
        }

        private List<SqlParameter> ExecuteNonQueryDistrictsToUpdate(District districtObj)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            SqlParameter para;
            para = new SqlParameter("@DistrictId", SqlDbType.Int);
            para.Value = districtObj.DistrictId;
            paraList.Add(para);
            para = new SqlParameter("@DistrictName", SqlDbType.NVarChar);
            para.Value = districtObj.DistrictName;
            paraList.Add(para);
            para = new SqlParameter("@DCityId", SqlDbType.Int);
            para.Value = districtObj.CityId;
            paraList.Add(para);

            return paraList;
        }
    }
}